#from sqlalchemy.sql import text
from utils import text_autocommit

create_or_replace_plpgsql = text_autocommit("""
CREATE OR REPLACE FUNCTION make_plpgsql()
RETURNS VOID
LANGUAGE SQL
AS $$
CREATE LANGUAGE plpgsql;
$$;
SELECT
     CASE
     WHEN EXISTS(
     SELECT 1
     FROM pg_catalog.pg_language
     WHERE lanname='plpgsql'
    )
    THEN NULL
    ELSE make_plpgsql() END;
""")

create_decode_genotype_function = text_autocommit("""
CREATE  OR REPLACE FUNCTION decode_genotype(int, text, text)
RETURNS text
AS $$
SELECT case when $1 = -1 then '0 0'
when $1 = 0 then $2 || ' ' || $2
when $1 = 1 then $2 || ' ' || $3
when $1 = 2 then $3 || ' ' || $3
else NULL
END;
$$ LANGUAGE sql;
""")

create_drop_constraint_if_exists_function = text_autocommit("""
CREATE OR REPLACE FUNCTION drop_constraint_if_exists (t text, k text, s text = NULL) returns void as $$
BEGIN
IF s IS NOT NULL
THEN
EXECUTE 'alter table ' || quote_ident(s) || '.' || quote_ident(t) || ' drop constraint ' || quote_ident(k) || ' cascade ';
ELSE
EXECUTE 'alter table ' || quote_ident(t) || ' drop constraint ' || quote_ident(k) || ' cascade ';
END IF;
EXCEPTION WHEN undefined_object THEN
END;
$$ LANGUAGE plpgsql;
""")
